<?php
/** Tiehui.Lu 512701323@qq.com version:1.0 utf-8 上午10:00:01 2009-1-12 */

/*****************************************************************
	CONNECTION MYSQL DATABASE PUBLIC CALSS 
		
	$host = localhost
	$user = username
	$pass = password
	$linkID = link id to connection
	$dbID = link id to database
	$db = connect object
	
	$result = link to query
	$numrows = number of rows contained in query
	$numfields = number of fields contained in query
	$affected_rows = 0; affected by the number of rows
	$last_query = array();	last query for more information
  
	Example Code:
	---------------------------------
	$db = new db("username", "password");             +++ CONNECT TO MYSQL +++
	$db->set_db("name of table");                     +++ SET THE CURRENT DATABASE +++
	
	$db->closequery();                                +++ FREE MEMORY ASSOCIATED WITH QUERY #1 +++
	$db->close();                                     +++ CLOSE CONNECTION TO DATABASE;

 *****************************************************************/

class db {
	/* --- DEFINE THE VARIABLES THAT ARE USED WITHIN THE CLASS --- */
	private $host = "localhost";
	private $user;
	private $pass;
	private $linkID;
	private $dbID;
	private $db;
	
	private $result;
	private $affected_rows = 0;
	private $numrows;
	private $numfields;
	private $last_query = array ();
	
	private $charset = "utf8";	// Input Database
	private $charset2 = "gb2312";// Output From Database
	
	/* --- DEFINE CONSTRUCTOR FUNCTION --- */
	function db($host, $user, $pass) {
		$this->host = $this->set_host ( $host );
		$this->user = $this->set_user ( $user );
		$this->pass = $this->set_pass ( $pass );
		if (! $this->linkID)
			$this->connect ();
	}
	
	/* --- DEFINE METHOD FUNCTIONS --- */
	function set_host($value) {
		return $this->host = $value;
	}
	
	function set_user($value) {
		return $this->user = $value;
	}
	
	function set_pass($value) {
		return $this->pass = $value;
	}
	
	function get_user() {
		return $this->user;
	}
	
	function connect() {
		if (! $this->linkID = @mysql_connect ( $this->host, $this->user, $this->pass )) //if (!$this->linkID = @mysql_pconnect($this->host,$this->user,$this->pass))
{
			$this->printError ( mysql_errno ( $this->linkID ), mysql_error ( $this->linkID ) );
		}
	}
	
	function close() {
		if (empty ( $this->linkID )) {
			$this->printError ( "NA", "There are no current mysql connections to close." );
		}
		mysql_close ( $this->linkID );
	}
	
	/**
	 * Traversal of the current server, get all database list
	 *
	 * @return Array()
	 */
	function list_dbs() {
		if (! $this->dbs = mysql_list_dbs ( $this->linkID )) {
			$this->printError ( mysql_errno ( $this->linkID ), mysql_error ( $this->linkID ) );
		}
		while ( $row = mysql_fetch_object ( $this->dbs ) ) {
			$arrDBList [] = $row->Database;
		}
		return $arrDBList;
	}
	
	function set_db($value) {
		if (! $this->dbID = mysql_select_db ( $value, $this->linkID )) {
			$this->printError ( mysql_errno ( $this->linkID ), mysql_error ( $this->linkID ) );
		}
		$this->db = $value;
	}
	
	/**
	 * Traversal of the current database, get all table list
	 *
	 * @return Array()
	 */
	function list_tables() {
		if (! $this->tables = mysql_list_tables ( $this->db )) {
			$this->printError ( mysql_errno ( $this->linkID ), mysql_error ( $this->linkID ) );
		}
		while ( $row = mysql_fetch_row ( $this->tables ) ) {
			$arrTableList [] = $row [0];
		}
		return $arrTableList;
	}
	
	/**
	 * Traversal of the set table, get all cloumn list
	 *
	 * @param array() $table
	 * @param array() $cloumn
	 * @return array()
	 */
	function list_cloumn($table = null, $cloumn = null) {
		if ($table == null || $table == "") {
			$tables = $this->list_tables ();
		} elseif (is_array ( $table )) {
			$tables = $table;
		} else {
			$tables = array ($table );
		}
		
		if ($cloumn == null || $cloumn == "") {
			$c = true;
		} elseif (is_array ( $cloumn )) {
			$c = $cloumn;
		} else {
			$c = array ($cloumn );
		}
		
		for($i = 0; $i < count ( $tables ); $i ++) {
			$cloumns = array ();
			if (! $this->cloumns = mysql_query ( "SHOW COLUMNS FROM $tables[$i]" )) {
				$this->printError ( mysql_errno ( $this->linkID ), mysql_error ( $this->linkID ) );
			}
			while ( $row = mysql_fetch_assoc ( $this->cloumns ) ) {
				if (is_array ( $c ) && in_array ( $row ["Field"], $c )) {
					$cloumns [] = $row;
				} elseif (! is_array ( $c )) {
					$cloumns [] = $row;
				}
			}
			$arrCloumnList [$tables [$i]] = $cloumns;
			unset ( $cloumns );
		}
		return $arrCloumnList;
	}
	
	/**
	 * Traversal of the set table, get all cloumn list
	 *
	 * @param string $key
	 * @param array() $table
	 * @param array() $cloumn
	 * @return array()
	 */
	function search($key, $page = 1, $size = 10, $table = null, $cloumn = null) {
		$startTime = $this->getmicrotime ();
		$result = array ();
		if ($key == "") {
			return NULL;
		}
		$start = ($page - 1) * $size;
		$result [0] ["key"] = $key;
		$result [0] ["page"] = $page;
		$result [0] ["size"] = $size;
		$result [0] ["from"] = $start + 1;
		$cloumns = $this->list_cloumn ( $table, $cloumn );
		$tables = array_keys ( $cloumns );
		for($i = 0; $i < count ( $tables ); $i ++) {			
			$tbl = $tables [$i];
			for($j = 0; $j < count ( $cloumns [$tbl] ); $j ++) {
				$w [] = "`" . $cloumns [$tbl] [$j] ['Field'] . "` LIKE '%$key%' ";
				$clo [] = $cloumns [$tbl] [$j] ['Field'];
			}
			if ($w != "" || isset ( $w )) {
				$w = join ( " OR ", $w );
			} else {
				break;
			}
			$total = $this->getTotalData ( $tbl, $w );
			$globalTotal += $total;
			$judge = $globalTotal - ($start + $size);
			$s1 = $start - ($globalTotal - $total);
			if ($judge >= 0 && $s1 >= 0) {
				$sql = "SELECT * FROM `$tbl` WHERE $w limit " . $s1 . "," . $size;
				$currRes = $this->query ( $sql );
				$count = count ( $result );
				$result [$count] ['table'] = $tbl;
				$result [$count] ['cloumns'] = $clo;
				$result [$count] ['total'] = count ( $currRes );
				$result [$count] ['data'] = $currRes;
			} elseif ($judge < 0 && $globalTotal > $start) {
				$t1 = $total - $s1;
				$start = $start + $t1;
				$size = $size - $t1;
				$sql = "SELECT * FROM `$tbl` WHERE $w limit " . $s1 . "," . $t1;
				$currRes = $this->query ( $sql );
				$count = count ( $result );
				$result [$count] ['table'] = $tbl;
				$result [$count] ['cloumns'] = $clo;
				$result [$count] ['total'] = count ( $currRes );
				$result [$count] ['data'] = $currRes;
			}
			unset ( $currRes );
			unset ( $sql );
			unset ( $clo );
			unset ( $w );
		}
		$endTime = $this->getmicrotime ();
		$result [0] ['to'] = ($judge >= 0) ? $start + $size : $globalTotal;
		$result [0] ['total'] = $globalTotal;
		$result [0] ['time'] = $endTime - $startTime;
		return $result;
	}
	
	function query($sql) {
		if (! isset ( $this->db ) || empty ( $this->db ))
			$this->killClass ();
		else {
			$this->last_query ["start_time"] = $this->getmicrotime ();
			mysql_query ( "SET NAMES '$this->charset'" );
			mysql_query ( "SET character_set_results='$this->charset2'" );
			if (! $this->result = mysql_query ( $sql )) {
				$this->printError ( mysql_errno ( $this->linkID ), mysql_error ( $this->linkID ) );
			}
			$this->last_query ["end_time"] = $this->getmicrotime ();
			$this->affected_rows = mysql_affected_rows ( $this->linkID );
			$this->numrows = mysql_num_rows ( $this->result );
			$this->numfields = mysql_num_fields ( $this->result );
			$this->last_query ["sql"] = $sql;
			$row_data = array ();
			if ($this->affected_rows != 0) {
				while ( $row = mysql_fetch_array ( $this->result ) ) {
					$row_data [] = $row;
				}
			}
			return $row_data;
		}
		return false;
	}
	
	function updateData($sql) {
		if (! isset ( $this->db ) || empty ( $this->db ))
			$this->killClass ();
		else {
			$this->last_query ["start_time"] = $this->getmicrotime ();
			mysql_query ( "SET NAMES '$this->charset'" );
			if (! $this->result = mysql_query ( $sql )) {
				$this->printError ( mysql_errno ( $this->linkID ), mysql_error ( $this->linkID ) );
				return false;
			} else {
				$this->last_query ["end_time"] = $this->getmicrotime ();
				return true;
			}
		}
		return false;
	}
	
	function insertData($sql) {
		if (! isset ( $this->db ) || empty ( $this->db ))
			$this->killClass ();
		else {
			mysql_query ( "SET NAMES '$this->charset'" );
			mysql_query ( $sql ) or die ( mysql_error () );
			return mysql_insert_id ();
		}
		return false;
	}
	
	function getData($tableName, $condition = "1", $column = "*", $orderby = "id", $taxis = "asc", $limit = "") {
		$sql = " select $column from " . $tableName . " where $condition order by $orderby $taxis $limit";
		return $this->query ( $sql );
	}
	
	function getDataByPage($page = 1, $size = 10, $tableName, $condition = "1", $order = "id", $taxis = "asc") {
		$start = ($page - 1) * $size;
		$info = array ();
		$info [0] ["page"] = $page;
		$info [0] ["size"] = $size;
		$info [0] ["from"] = $start + 1;
		$total = $this->getTotalData ( $tableName, $condition );
		$info [0] ["to"] = ($total >= $page * $size) ? $page * $size : $total;
		$info [0] ["total"] = $total;
		$sql = " select * from " . $tableName . " where $condition order by $order $taxis limit " . $start . "," . $size;
		$data = $this->query ( $sql );
		$data = array_merge ( $info, $data );
		return $data;
	}
	
	function getTotalData($tableName, $condition = "1") {
		$sql = " select count(*) count from `" . $tableName . "` where $condition";
		$count = $this->query ( $sql );
		return $count [0] ["count"];
	}
	
	//need test
	function count($sql) {
		$this->result = mysql_query ( $sql );
		return mysql_num_rows ( $this->result );
	}
	
	function closequery() {
		mysql_free_result ( $this->result );
	}
	
	function query_info() {
		echo "<u>Your Previous Query Consisted of:</u><br>";
		echo "SQL = '" . $this->last_query ["sql"] . "'<br>";
		$temp = ($this->last_query ["end_time"] - $this->last_query ["start_time"]);
		$temp *= 1000;
		$temp = number_format ( $temp, 3 );
		echo "Time Elapsed: " . $temp . "(ms)<br>";
		echo "Number of Records: " . $this->numrows . "<br>";
		echo "Number of Rows Affected: " . $this->affected_rows;
	}
	
	function getmicrotime() {
		list ( $usec, $sec ) = explode ( " ", microtime () );
		return (( float ) $usec + ( float ) $sec);
	}
	
	function killClass() {
		echo "You cannot make a query without first creating a database connection.";
		exit ();
	}
	
	function printError($errNum, $errText) {
		echo (sprintf ( "Error (%s): %s<p>", $errNum, $errText ));
		exit ();
	}
}
;
?>